package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;
import java.util.logging.Level;
import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MInventory;
import org.compiere.model.MOrg;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.eevolution.model.MHRJob;
import org.utils.AnalogConvertor_RU;
import extend.org.compiere.utils.Util;

public class MM_Form_Inv7 extends SvrProcess {
	/*Current context*/
	private Properties m_ctx ;
	
	private ProcessInfo pi ;
	
	private int p_mol, p_chiefComm, p_memberComm1, p_memberComm2;
	
	Date p_DateDoc;
	
	String ResultPath, p_DocumentBaseNo, p_Record_ID;
	
	/*
	* Prepare - e.g., get Parameters.
	*/
	@Override
	protected void prepare() {
	log.info( "");
	m_ctx = Env. getCtx();
	pi = getProcessInfo();
	ProcessInfoParameter[] para = getParameter();
	for (int i = 0; i < para.length; i++) {
		String name = para[i].getParameterName();
		if (para[i].getParameter() == null)
			;
		else if (name.equals("M_Inventory_ID"))
			p_Record_ID = (String) para[i].getParameter();
		else if (name.equals("mol"))
			p_mol = para[i].getParameterAsInt();
		else if (name.equals("chiefComm"))
			p_chiefComm = para[i].getParameterAsInt();
		else if (name.equals("memberComm1"))
			p_memberComm1 = para[i].getParameterAsInt();
		else if (name.equals("memberComm2"))
			p_memberComm2 = para[i].getParameterAsInt();
		else if (name.equals("DocumentBaseNo"))
			p_DocumentBaseNo = (String) para[i].getParameter();
		else if (name.equals("DateDoc"))
			p_DateDoc = (Date) para[i].getParameter();
		else
			log.log(Level. SEVERE, "Unknown Parameter: " + name);
		}
	}
	
	@Override
	protected String doIt() throws Exception {
		//File path
		ResultPath = "Form_Inv7_" ;
		Integer pathI = 0;
		boolean flag = true ;
		while (flag) {
			File f = new File(Util.localFilePath + ResultPath + ".xlsx"); 
			try {
				if (f.exists() && !f.delete()) {
					ResultPath = ResultPath + "(" ;
					ResultPath = ResultPath .substring(0, 11) + (++pathI) + ")";
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				ResultPath = ResultPath + "(" + (++pathI) + ")";
			}
		}
		ResultPath = Util.localFilePath + ResultPath + ".xlsx" ;
		MAttachmentEntry entry = Util. getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate (m_ctx , "NotFoundTemplate"));

		Workbook wb = new XSSFWorkbook(entry.getInputStream());
		Sheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		
		//Cell Styling
		CellStyle mergeStyle = wb.createCellStyle();
		mergeStyle.setBorderBottom(( short)1);
		mergeStyle.setBorderTop(( short)1);
		mergeStyle.setBorderLeft(( short)1);
		mergeStyle.setBorderRight(( short)1);
		mergeStyle.setFont(wb.getFontAt(sheet.getRow(9).getCell(0).getCellStyle().getFontIndex()));
		mergeStyle.setWrapText( true);
		mergeStyle.setVerticalAlignment(CellStyle. VERTICAL_CENTER);
		
		//Header
		sheet.getRow(9).getCell(21).setCellValue(MOrg. get(m_ctx, Env.getAD_Org_ID(m_ctx)).getName());
		sheet.getRow(9).getCell(61).setCellValue((new MBPartner(m_ctx , MOrg.get(m_ctx, Env.getAD_Org_ID(m_ctx )).getLinkedC_BPartner_ID(get_TrxName()), get_TrxName()).getFI_BIN_IIN()));
		
//		MInventory inv = new MInventory(m_ctx, p_M_Inventory_ID, get_TrxName());
//		sheet.getRow(12).getCell(44).setCellValue(inv.getDocumentNo());
		sheet.getRow(13).getCell(53).setCellValue(p_Record_ID);
//		sheet.getRow(12).getCell(54).setCellValue((new SimpleDateFormat("dd.MM.yyyy").format(inv.getMovementDate())));
		sheet.getRow(24).getCell(21).setCellValue((new SimpleDateFormat("dd").format(p_DateDoc)));
		sheet.getRow(24).getCell(24).setCellValue((new SimpleDateFormat("MMMMM").format(p_DateDoc)));
		sheet.getRow(24).getCell(32).setCellValue((new SimpleDateFormat("yyyy").format(p_DateDoc)));
		sheet.getRow(24).getCell(38).setCellValue(p_DocumentBaseNo);
//		
//		sheet.getRow(28).getCell(32).setCellValue((new SimpleDateFormat("dd").format(inv.getMovementDate())));
//		sheet.getRow(28).getCell(35).setCellValue((new SimpleDateFormat("MMMMM").format(inv.getMovementDate())));
//		sheet.getRow(28).getCell(43).setCellValue((new SimpleDateFormat("yyyy").format(inv.getMovementDate())));
//		
//		//nacalo inventory
//		sheet.getRow(30).getCell(12).setCellValue((new SimpleDateFormat("dd").format(inv.getMovementDate())));
//		sheet.getRow(30).getCell(15).setCellValue((new SimpleDateFormat("MMMMM").format(inv.getMovementDate())));
//		sheet.getRow(30).getCell(23).setCellValue((new SimpleDateFormat("yyyy").format(inv.getMovementDate())));
//		//konets inventory
//		sheet.getRow(30).getCell(48).setCellValue((new SimpleDateFormat("dd").format(inv.getMovementDate())));
//		sheet.getRow(30).getCell(51).setCellValue((new SimpleDateFormat("MMMMM").format(inv.getMovementDate())));
//		sheet.getRow(30).getCell(59).setCellValue((new SimpleDateFormat("yyyy").format(inv.getMovementDate())));
		
		
		//Document Header
		String sqlheader = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_mol;
		int id = DB.getSQLValue(get_TrxName(), sqlheader);
		sheet.getRow(21).getCell(10).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(21).getCell(29).setCellValue((new MBPartner(m_ctx, p_mol, get_TrxName()).getName()));
		sheet.getRow(63).getCell(29).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(63).getCell(48).setCellValue((new MBPartner(m_ctx, p_mol, get_TrxName()).getName()));
//		sheet.getRow(65).getCell(4).setCellValue((new SimpleDateFormat("dd").format(inv.getMovementDate())));
//		sheet.getRow(65).getCell(7).setCellValue((new SimpleDateFormat("MMMMM").format(inv.getMovementDate())));
//		sheet.getRow(65).getCell(15).setCellValue((new SimpleDateFormat("yyyy").format(inv.getMovementDate())));
		sqlheader = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_chiefComm;
		id = DB.getSQLValue(get_TrxName(), sqlheader);
		sheet.getRow(51).getCell(13).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(51).getCell(32).setCellValue((new MBPartner(m_ctx, p_chiefComm, get_TrxName()).getName()));
		sqlheader = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberComm1;
		id = DB.getSQLValue(get_TrxName(), sqlheader);
		sheet.getRow(54).getCell(13).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(54).getCell(32).setCellValue((new MBPartner(m_ctx, p_memberComm1, get_TrxName()).getName()));
		sqlheader = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberComm2;
		id = DB.getSQLValue(get_TrxName(), sqlheader);
		sheet.getRow(57).getCell(13).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(57).getCell(32).setCellValue((new MBPartner(m_ctx, p_memberComm2, get_TrxName()).getName()));
		
		//Positions
//		PreparedStatement ps = null;
//		ResultSet rs = null;
//		String sql = "SELECT p.name AS product " + 
//							", ev.value AS account " + 
//							", p.value AS product_number " + 
//							", uom.name AS uom " + 
//							", round(fa.amtacctdr / abs(iline.qtycount - iline.qtybook), 2) AS colprice" +  
//							", iline.qtycount " + 
//							", iline.qtycount * round(fa.amtacctdr / abs(iline.qtycount - iline.qtybook), 2) AS amtcount " + 
//							", iline.qtybook " + 
//							", iline.qtybook * round(fa.amtacctdr / abs(iline.qtycount - iline.qtybook), 2) AS amtbook " + 
//						"FROM fact_acct fa " + 
//						"JOIN m_inventory ihead ON fa.record_id = ihead.m_inventory_id " + 
//						"JOIN m_inventoryline iline ON fa.record_id = iline.m_inventory_id AND fa.line_id = iline.m_inventoryline_id " + 
//						"LEFT JOIN m_product p ON p.m_product_id = iline.m_product_id " + 
//						"LEFT JOIN c_uom_trl uom ON uom.c_uom_id = p.c_uom_id " + 
//						"LEFT JOIN c_elementvalue ev ON ev.c_elementvalue_id = fa.account_id " + 
//						"WHERE fa.ad_table_id = 321::numeric " + 
//							"AND ihead.docstatus <> 'VO'::bpchar " + 
//							"AND ihead.docstatus <> 'RE'::bpchar " + 
//							"AND fa.counter < fa.reference " + 
//							"AND ev.value::text !~~ '%ТС'::text " + 
//							"AND iline.inventorytype = 'D' " + 
//							"AND ihead.m_inventory_id = " + p_M_Inventory_ID;
//		
//		int row = 41;
//		int no = 0, totalQty = 0;
//		double totalAmt = 0;
//		
//		try {
//			ps = DB.prepareStatement(sql.toString(), get_TrxName());
//			rs = ps.executeQuery();
//			
//			while (rs.next()) {
//				//Shifting and Merging
//				sheet.shiftRows(row, row + 26, 1);
//				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 2));
//				sheet.addMergedRegion(new CellRangeAddress(row, row, 3, 15));
//				sheet.addMergedRegion(new CellRangeAddress(row, row, 16, 21));
//				sheet.addMergedRegion(new CellRangeAddress(row, row, 22, 26));
//				sheet.addMergedRegion(new CellRangeAddress(row, row, 27, 31));
//				sheet.addMergedRegion(new CellRangeAddress(row, row, 32, 36));
//				sheet.addMergedRegion(new CellRangeAddress(row, row, 37, 42));
//				sheet.addMergedRegion(new CellRangeAddress(row, row, 43, 50));
//				sheet.addMergedRegion(new CellRangeAddress(row, row, 51, 56));
//				sheet.addMergedRegion(new CellRangeAddress(row, row, 57, 64));
//				
//				R = sheet.createRow(row);
//				for(int i = 0; i <= 64; i++){
//					C = R.createCell(i);
//					C.setCellStyle(mergeStyle);
//				}
//				
//				sheet.getRow(row).getCell(0).setCellValue(++no);
//				sheet.getRow(row).getCell(3).setCellValue(rs.getString("product"));
//				sheet.getRow(row).getCell(16).setCellValue(rs.getString("account"));
//				sheet.getRow(row).getCell(22).setCellValue(rs.getString("product_number"));
//				sheet.getRow(row).getCell(27).setCellValue(rs.getString("uom"));
//				sheet.getRow(row).getCell(32).setCellValue(rs.getDouble("colprice"));
//				sheet.getRow(row).getCell(37).setCellValue(rs.getInt("qtycount"));
//				totalQty += rs.getInt("qtycount");
//				sheet.getRow(row).getCell(43).setCellValue(rs.getDouble("amtcount"));
//				totalAmt += rs.getDouble("amtcount");
//				sheet.getRow(row).getCell(51).setCellValue(rs.getInt("qtybook"));
//				sheet.getRow(row).getCell(57).setCellValue(rs.getDouble("amtbook"));
//				
//				row++;
//			}
//		} catch (Exception e) {
//			log.log(Level.SEVERE, sql.toString(), e);
//		} finally {
//			DB.close(rs, ps);
//			rs = null;
//			ps = null;
//		}
//		
//		sheet.getRow(row).getCell(37).setCellFormula("SUM(AL42:AL" + row + ")");
//		sheet.getRow(row).getCell(43).setCellFormula("SUM(AR42:AR" + row + ")");
//		sheet.getRow(row).getCell(51).setCellFormula("SUM(AZ42:AZ" + row + ")");
//		sheet.getRow(row).getCell(57).setCellFormula("SUM(BF42:BF" + row + ")");
//		
//		sheet.getRow(row + 2).getCell(20).setCellValue(AnalogConvertor_RU.toString(no));
//		sheet.getRow(row + 5).getCell(24).setCellValue(AnalogConvertor_RU.toString(totalQty));
//		sheet.getRow(row + 8).getCell(24).setCellValue(AnalogConvertor_RU.toString(totalAmt));
//		
//		sheet.getRow(row + 20).getCell(39).setCellValue(no);
		
		//--------------------------------------------------------------------------
		//Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();
		
		//Open the file with the application associated
		Util. launchFile(new File(ResultPath));
		
		return Msg.translate(m_ctx, "Success");
	}	
}
